const express = require("express");
const router = express.Router();
const { Production, User, SizeRule, sequelize } = require("../../models");
const { Op } = require("sequelize");
const { NotFound } = require("http-errors");
const { success, failure } = require("../../utils/responses");
const { userInfo } = require("../../utils/userInfo");
const { resetAllProductionsStatus } = require("../../utils/getProduction");

// 为常用查询字段添加数据库索引建议（需要在数据库中执行）
// 建议创建的索引:
// 1. CREATE INDEX idx_production_sn ON Productions(sn);
// 2. CREATE INDEX idx_production_status ON Productions(status);
// 3. CREATE INDEX idx_production_beused ON Productions(beUsed);
// 4. CREATE INDEX idx_production_line_name ON Productions(lineName);
// 5. CREATE INDEX idx_production_created_at ON Productions(createdAt);
// 6. CREATE INDEX idx_production_composite_status_sn ON Productions(status, sn);

/**
 * 查询产品列表
 * GET /admin/production
 */
router.get("/", async (req, res, next) => {
  try {
    const query = req.query;
    const currentPage = Math.abs(Number(query.currentPage)) || 1;
    const pageSize = Math.abs(Number(query.pageSize)) || 10;
    const offset = (currentPage - 1) * pageSize;

    const condition = {
      where: {},
      attributes: { exclude: ["UserId"] },
      include: [
        {
          model: User,
          as: "user",
          attributes: ["id", "username"],
        },
      ],
      order: [["id", "DESC"]],
      limit: pageSize,
      offset: offset,
    };

    // 优化：使用精确匹配而不是模糊匹配，除非必要
    if (query.status) {
      if (query.status === "999") {
        condition.where.status = {
          [Op.ne]: "1",
        };
      } else {
        condition.where.status = query.status;
      }
    }

    // 优化：添加索引支持的查询条件
    if (query.long) {
      condition.where.long = query.long;
    }

    if (query.beUsed) {
      condition.where.beUsed = query.beUsed;
    }

    // 优化：SN精确查询性能更好，仅在必要时使用模糊查询
    if (query.sn) {
      // 如果SN是完整精确匹配，使用精确查询提升性能
      if (query.snExact === "true") {
        condition.where.sn = query.sn;
      } else {
        // 模糊查询（性能较低，谨慎使用）
        condition.where.sn = {
          [Op.like]: `%${query.sn}%`,
        };
      }
    }

    // 优化：日期范围查询
    if (query.startDate && query.endDate) {
      condition.where.createdAt = {
        [Op.between]: [query.startDate, query.endDate],
      };
    }

    if (query.lineName) {
      condition.where.lineName = query.lineName;
    }

    // 优化：使用findAndCountAll的性能选项
    const { count, rows } = await Production.findAndCountAll({
      ...condition,
      distinct: true, // 确保关联查询时计数准确
    });

    success(req, res, "查询产品列表成功。", {
      list: rows,
      pagination: {
        total: count,
        currentPage,
        pageSize,
      },
    });
  } catch (error) {
    failure(req, res, error);
  }
});

/**
 * 查询产品列表详情
 * GET /admin/production
 */
router.get("/:id", async (req, res, next) => {
  try {
    // 优化：直接通过主键查询，性能最佳
    const { id } = req.params;

    // 验证ID是否为有效数字
    if (!id || isNaN(Number(id))) {
      throw new NotFound(`无效的产品ID: ${id}。`);
    }

    const productionDetail = await Production.findByPk(id, {
      include: [
        {
          model: User,
          as: "user",
          attributes: ["id", "username"],
        },
      ],
    });

    if (!productionDetail) {
      throw new NotFound(`ID: ${id}的产品表未找到。`);
    }

    success(req, res, "查询产品成功。", {
      productionDetail,
    });
  } catch (error) {
    failure(req, res, error);
  }
});

/**
 * 更新产品
 * PUT /admin/production
 */
router.put("/:id", async (req, res, next) => {
  try {
    // 优化：直接通过主键查询，性能最佳
    const { id } = req.params;

    // 验证ID是否为有效数字
    if (!id || isNaN(Number(id))) {
      throw new NotFound(`无效的产品ID: ${id}。`);
    }

    const productionDetail = await Production.findByPk(id);

    if (!productionDetail) {
      throw new NotFound(`ID: ${id}的产品表未找到。`);
    }

    if (productionDetail.status == 1) {
      throw new NotFound("产品已完成，无法修改。");
    }

    const body = await filterBody(req);
    await productionDetail.update(body);
    success(req, res, "更新产品成功。", {
      productionDetail,
    });
  } catch (error) {
    failure(req, res, error);
  }
});

// 恢复数据状态
router.put("/upOrigin/:id", async (req, res, next) => {
  try {
    // const productionDetail = await getProductionItem(req);

    // const body = await filterBody(req);
    // body.status = 0;
    // await productionDetail.update(body);
    await resetAllProductionsStatus();

    success(req, res, "更新产品成功。", {});
  } catch (error) {
    failure(req, res, error);
  }
});

/**
 * 创建产品
 * POST /admin/production
 */
router.post("/", async (req, res, next) => {
  try {
    console.log("body", req.body);
    const body = await filterBody(req);
    // 获取尺寸列表
    const sizeRuleDetail = await getSizeRule(req.body.sizeRuleId);

    const data = await checkRule(body, sizeRuleDetail);

    const productionDetail = await Production.create(data);
    success(req, res, "创建产品成功。", {
      productionDetail,
    });
  } catch (error) {
    failure(req, res, error);
  }
});

/**
 * 批量导入产品
 * POST /admin/production/batch
 */
router.post("/batch", async (req, res, next) => {
  try {
    const list = req.body.list;

    if (list.length == 0) {
      throw new NotFound("商品列表不能为空。");
    }
    const body = await filterBody(req, list);

    // 检查SN是否已存在 - 优化：使用单次查询检查所有SN
    const snList = body.map((item) => item.sn);
    const existingProductions = await Production.findAll({
      where: {
        sn: {
          [Op.in]: snList,
        },
      },
      attributes: ["sn"],
    });

    if (existingProductions.length > 0) {
      const existingSNs = existingProductions.map((p) => p.sn);
      throw new NotFound(
        `请检查${existingSNs.join(", ")}sn，目前数据库中已经存在该数据`
      );
    }

    const batchList = [];
    for (let i = 0; i < body.length; i++) {
      // 获取尺寸列表
      console.log("body[i]", body[i]);

      const sizeRuleDetail = await getSizeRule(body[i].sizeRuleId);
      console.log("sizeRuleDetail", sizeRuleDetail);
      const data = await checkRule(body[i], sizeRuleDetail);
      batchList.push(data);
    }

    // 优化：使用事务确保数据一致性
    await sequelize.transaction(async (t) => {
      await Production.bulkCreate(batchList, {
        transaction: t,
      });
      success(req, res, "批量导入产品成功。");
    });
  } catch (error) {
    failure(req, res, error);
  }
});

/**
 * 快速查询接口 - 专门用于高频查询场景
 * GET /admin/production/fast-search
 */
router.get("/fast-search", async (req, res, next) => {
  try {
    const query = req.query;

    // 只支持高频查询字段，避免复杂关联查询
    const whereConditions = {};

    // 优化：只允许精确匹配查询，提高性能
    if (query.sn) {
      whereConditions.sn = query.sn;
    }

    if (query.status) {
      whereConditions.status = query.status;
    }

    if (query.beUsed) {
      whereConditions.beUsed = query.beUsed;
    }

    if (query.palletCode) {
      whereConditions.palletCode = query.palletCode;
    }

    // 如果没有查询条件，返回错误
    if (Object.keys(whereConditions).length === 0) {
      return failure(req, res, new Error("至少需要提供一个查询条件"));
    }

    // 优化：只查询必要的字段，避免加载不必要的数据
    const attributes = query.fields
      ? query.fields.split(",")
      : [
          "id",
          "sn",
          "long",
          "wide",
          "height",
          "status",
          "beUsed",
          "lineName",
          "createdAt",
        ];

    // 优化：设置合理的查询限制
    const limit = Math.min(Math.abs(Number(query.limit)) || 10, 100);

    const results = await Production.findAll({
      where: whereConditions,
      attributes: attributes,
      order: [["id", "DESC"]],
      limit: limit,
    });

    success(req, res, "快速查询成功。", {
      list: results,
      count: results.length,
    });
  } catch (error) {
    failure(req, res, error);
  }
});

/**
 * 统计接口 - 用于获取数据概览
 * GET /admin/production/stats
 */
router.get("/stats", async (req, res, next) => {
  try {
    // 优化：使用聚合查询获取统计数据，避免加载大量数据
    const [statusStats, beUsedStats, recentCount] = await Promise.all([
      // 按状态统计
      Production.findAll({
        attributes: [
          "status",
          [sequelize.fn("COUNT", sequelize.col("status")), "count"],
        ],
        group: ["status"],
        raw: true,
      }),

      // 按beUsed统计
      Production.findAll({
        attributes: [
          "beUsed",
          [sequelize.fn("COUNT", sequelize.col("beUsed")), "count"],
        ],
        group: ["beUsed"],
        raw: true,
      }),

      // 最近7天创建的数量
      Production.count({
        where: {
          createdAt: {
            [Op.gte]: new Date(new Date() - 7 * 24 * 60 * 60 * 1000),
          },
        },
      }),
    ]);

    success(req, res, "统计查询成功。", {
      statusStats,
      beUsedStats,
      recentCount,
    });
  } catch (error) {
    failure(req, res, error);
  }
});

async function checkRule(body, sizeRuleDetail) {
  const {
    long,
    wide,
    height,
    sn,
    trayCode,
    lineName,
    sizeRuleId,
    beUsed,
    userName,
    userId,
    weight,
    palletCode,
    productCode,
    sono,
    soline,
  } = body;
  const {
    long: sizeRuleLong,
    wide: sizeRuleWide,
    height: sizeRuleHeight,
    presetsNum,
    layerNum,
    palletizingNum,
    grippingDirection,
    leftAndRightFaultToleranceNum,
    faultToleranceBeforeAndAfterNum,
  } = sizeRuleDetail.dataValues;
  // 检查长、宽、高是否在容差范围内（正负30）
  if (Math.abs(long - sizeRuleLong) > 30) {
    throw new NotFound(
      `长与尺寸表不匹配。长度差值是${Math.abs(long - sizeRuleLong)}`
    );
  }
  if (Math.abs(wide - sizeRuleWide) > 30) {
    throw new NotFound(
      `宽与尺寸表不匹配。宽度差值是${Math.abs(wide - sizeRuleWide)}`
    );
  }
  if (Math.abs(height - sizeRuleHeight) > 30) {
    throw new NotFound(
      `高与尺寸表不匹配。${Math.abs(height - sizeRuleHeight)}`
    );
  }
  const status = "0";
  const data = {
    long,
    wide,
    height,
    beUsed,
    lineName,
    sn,
    trayCode,
    sizeRuleId,
    presetsNum,
    layerNum,
    palletizingNum,
    grippingDirection,
    leftAndRightFaultToleranceNum,
    faultToleranceBeforeAndAfterNum,
    status,
    userName,
    userId,
    weight,
    palletCode,
    productCode,
    sono,
    soline,
  };
  return data;
}

/**
 * 公共方法：查询当前尺寸表
 */
async function getSizeRule(sizeRuleId) {
  console.log("sizeRuleId===", sizeRuleId);
  // 优化：直接通过主键查询，性能最佳
  if (!sizeRuleId || isNaN(Number(sizeRuleId))) {
    throw new NotFound(`无效的尺寸表ID: ${sizeRuleId}。`);
  }

  const sizeRule = await SizeRule.findByPk(sizeRuleId);

  if (!sizeRule) {
    throw new NotFound(`ID: ${sizeRuleId}的尺寸表未找到。`);
  }

  return sizeRule;
}

/**
 * 公共方法：白名单过滤
 * @param req
 * @returns
 */
async function filterBody(req, list = []) {
  let { user, originname } = await userInfo(req);
  if (list.length > 0) {
    for (let i = 0; i < list.length; i++) {
      list[i].userId = user.id;
      list[i].userName = user.username;
    }
    return list;
  }

  return {
    long: req.body.long,
    wide: req.body.wide,
    height: req.body.height,
    presetsNum: req.body.presetsNum,
    layerNum: req.body.layerNum,
    palletizingNum: req.body.palletizingNum,
    grippingDirection: req.body.grippingDirection,
    leftAndRightFaultToleranceNum: req.body.leftAndRightFaultToleranceNum,
    faultToleranceBeforeAndAfterNum: req.body.faultToleranceBeforeAndAfterNum,
    userId: user.id,
    userName: user.username,
    beUsed: req.body.beUsed,
    status: req.body.status,
    sn: req.body.sn,
    trayCode: req.body.trayCode,
    lineName: req.body.lineName,
    sizeRuleId: req.body.sizeRuleId,
    weight: req.body.weight,
    palletCode: req.body.palletCode,
    productCode: req.body.productCode,
    sono: req.body.sono,
    soline: req.body.soline,
  };
}

module.exports = router;
